﻿/******************************************************   
创建者: wdj
创建时间: 2017-10-18
修改:
修改时间: 
功能: 调拨结果统计
 ******************************************************/

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library;
using CommodityTransferAPI.Model;
using george.Helper;
using NHibernateVertica;
using com.nm.library.Helper;
using george.Data;
using System.Data;
using com.nm;

namespace CommodityTransferAPI
{
    [Description("调拨结果统计")]
    public class MvFullRateHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        /// Writer：wdj
        /// Create Date：2017-10-18
        [MustLogin]
        [Description("调拨结果统计列表")]
        [ReturnType(typeof(PaperData<MvFullRateModel.QueryModel>))]
        public ApiResult GetList(
            [ParamDescription("店铺ID")] string ShopIDStr = "",
            [ParamDescription("页码")] int Page = 1,
            [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var shopIDStr = ShopIDStr.GetString();
                var count = ReturnCount(shopIDStr);
                var result = PaperData<MvFullRateModel.QueryModel>.Create(count, Size, Page);
                var query = ReturnList(shopIDStr, Page, Size);

                var data = query.ToList();
                result.Items = data;
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }

        /// <summary>
        /// 返回调拨结果统计总条数
        /// </summary>
        /// <param name="ShopIDStr"></param>
        /// <returns></returns>
        /// Writer：wdj
        /// Create Date：2017-10-18
        public int ReturnCount(string ShopIDStr)
        {
            try
            {
                #region

                var sql = $@"select count(1) as count
from 
ngoods.ngoods_Mv_FullRate rate
left join dw.dw_Shop shop
on rate.CompanyID = shop.CompanyID
and rate.ShopID = shop.ShopID
WHERE
 rate.ExtrSource = '{UserInfo.UserExtrSource}' 
 and rate.CompanyID = '{UserInfo.UserCompanyId}' 
and rate.SuppleDate =  '{DateTime.Now.ToString("yyyy-MM-dd")}' 
{
                        (string.IsNullOrEmpty(ShopIDStr) ? "" : $" and rate.ShopId in {ShopIDStr} ")
                    }";

                #endregion

                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 返回调拨结果统计列表
        /// </summary>
        /// <param name="ShopIDStr"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        /// Writer：wdj
        /// Create Date：2017-10-18
        public IList<MvFullRateModel.QueryModel> ReturnList(string ShopIDStr, int Page, int Size)
        {
            try
            {
                var dt = ReturnDatatable(ShopIDStr);
                var list = ModelConvertHelper<MvFullRateModel.QueryModel>.ConvertToModel(dt);
                return list.ToList();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 返回调拨结果统计列表
        /// </summary>
        /// <param name="ShopIDStr"></param>
        /// <returns></returns>
        /// Writer：wdj
        /// Create Date：2017-10-18
        public DataTable ReturnDatatable(string ShopIDStr)
        {
            try
            {
                #region

                var sql = $@"select 
 shop.shopname,
rate.ExtrSource,
rate.CompanyID,
rate.SuppleDate,
OutBeforeFullRate,
OutBeforeBreakSizeFullRate,
OutSKCQty,
OutQty,
OutAfterFullRate,
OutAfterBreakSizeFullRate,
IntSKCQty,
IntQty,
ResultFullRate,
ResultBreakSizeFullRate
                from 
                ngoods.ngoods_Mv_FullRate rate
                left join dw.dw_Shop shop
                on rate.CompanyID = shop.CompanyID
                and rate.ShopID = shop.ShopID
                WHERE
                 rate.ExtrSource = '{UserInfo.UserExtrSource}' 
                 and rate.CompanyID = '{UserInfo.UserCompanyId}' 
                and rate.SuppleDate = '{DateTime.Now.ToString("yyyy-MM-dd")}' 
                    {
                        (string.IsNullOrEmpty(ShopIDStr) ? "" : $" and rate.ShopId in {ShopIDStr} ")
                    }";

                #endregion

                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return dt;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 返回调拨结果统计列表
        /// </summary>
        /// <param name="ShopIDStr"></param>
        /// <returns></returns>
        /// Writer：wdj
        /// Create Date：2017-10-18
        public DataTable ExportDatatable(string ShopIDStr)
        {
            try
            {
                #region
                var sql = $@"select 
 shop.shopname,
rate.ExtrSource,
rate.CompanyID,
rate.SuppleDate,
OutBeforeFullRate,
OutBeforeBreakSizeFullRate,
OutSKCQty,
OutQty,
OutAfterFullRate,
OutAfterBreakSizeFullRate,
IntSKCQty,
IntQty,
ResultFullRate,
ResultBreakSizeFullRate
                from 
                ngoods.ngoods_Mv_FullRate rate
                left join dw.dw_Shop shop
                on rate.CompanyID = shop.CompanyID
                and rate.ShopID = shop.ShopID
                WHERE
 rate.ExtrSource = '{UserInfo.UserExtrSource}' 
 and rate.CompanyID = '{UserInfo.UserCompanyId}' 
                and rate.SuppleDate = '{DateTime.Now.ToString("yyyy-MM-dd")}' 
                    {
                        (string.IsNullOrEmpty(ShopIDStr) ? "" : $"{ShopIDStr} ")
                    }";

                #endregion

                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return dt;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        public ApiResult UpdateMvJobAllMessage(MvJobAllMessageModel.ModifyListModel param)
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            var uid = Guid.NewGuid().ToString("N");
            var tempTableName = $@"ngoods.temp{uid}";
            try
            {
                if (param == null) return Error("参数对象为空");
                if (param.List != null)
                {
                    var result = param.List.Select(d => d.SizeList.Select(r => new MvJobAllMessageModel.SaveModel
                    {
                        CompanyID = d.CompanyID,
                        ExtrSource = d.ExtrSource,
                        InShopID = d.InShopID,
                        OutShopID = d.OutShopID,
                        MaterialID = d.MaterialID,
                        ProjectCode = d.ProjectCode,
                        SuppleDate = d.SuppleDate,
                        Qty = r.Qty,
                        SizeID = r.SizeID,
                        SizeName = r.SizeName,
                    }));
                    var dt = DataTableHelper.LinqToDataTable(result);
                    MergeMvJobAllMessage(dt, tempTableName);
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error(ex);
            }
            finally
            {
                Helper.DropTempTable(tempTableName);
            }
        }

        public void MergeMvJobAllMessage(DataTable dt, string TempTableName)
        {
            try
            {
                const string onConditions = @"targetTable.CompanyID=sourceTable.CompanyID
    and targetTable.ExtrSource=sourceTable.ExtrSource
	and targetTable.SuppleDate=sourceTable.SuppleDate
	and targetTable.ProjectCode=sourceTable.ProjectCode
	and targetTable.OutShopID=sourceTable.OutShopID
	and targetTable.InShopID=sourceTable.InShopID
	and targetTable.MaterialID=sourceTable.MaterialID
	and targetTable.SizeID=sourceTable.SizeID";
                var setConditions = $@"Qty=sourceTable.Qty";
                const string insertConditions =
                    @"ExtrSource,CompanyID,SuppleDate,ProjectCode,OutShopID,InShopID,MaterialID,SizeID,SizeName,Qty";
                const string valuesConditions =
                    @"sourceTable.ExtrSource,sourceTable.CompanyID,sourceTable.SuppleDate,sourceTable.ProjectCode,sourceTable.OutShopID,sourceTable.InShopID,sourceTable.MaterialID,sourceTable.SizeID,sourceTable.SizeName,sourceTable.Qty";
                Helper.Merge(TempTableName, "ngoods.ngoods_Mv_JobAllMessage", dt, onConditions, setConditions,
                    insertConditions,
                    valuesConditions, true);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：ljj
        /// Create Date：2017-10-14
        public DataTable ExportMvJobAllMessage(string whereStr)
        {
            try
            {
                #region  

                var sql = $@"SELECT  *
FROM    ( SELECT    *
          FROM      ngoods.vw_ngoods_JobAllMessage
          WHERE     ExtrSource = '{UserInfo.UserExtrSource}'
                    AND CompanyID = '{UserInfo.UserCompanyId}'
        ) mes
        INNER JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Material
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) mat ON mes.MaterialID = mat.MaterialID
                           AND mes.ExtrSource = mat.ExtrSource 
        WHERE  1=1  {whereStr}
ORDER BY  ProJectCode ,
        mes.MaterialID";

                #endregion

                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return dt;
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                throw (ex);
            }
        }
    }
}
